﻿using Hsp.Auto.Common.Data;
using Hsp.Auto.Common.Utility;
using Hsp.Auto.Interface;

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SQLite;
using System.Linq;
using System.Reflection;

namespace Hsp.Auto.SQLite
{
    /// <summary>
    /// Asp.Net Core NuGet引用SQLite
    /// System.Data.SQLite.Core
    /// V1.0.113.1
    /// </summary>
    public class SQLiteHelper : DataHelper, IDbHelper
    {
        #region 泛型方法查询实体数据

        /// <summary>
        /// 根据属性类型获取字段名称
        /// </summary>
        /// <param name="type"></param>
        /// <returns></returns>
        internal static string GetFiledsByType(Type type)
        {
            // User是关键字，因此要包成[User]
            return string.Join(',', type.GetProperties().Select(p => '[' + p.Name + ']'));
        }

        /// <summary>
        /// 泛型方法查询数据实体
        /// </summary>
        /// <typeparam name="T">泛型</typeparam>
        /// <param name="id">id</param>
        /// <returns></returns>
        public T SingleQuery<T>(int id)
        {
            Type type = typeof(T); // User是关键字，因此要包成[User]
            var strSql = SqlCache<T>.QueryString + $" AND (Id=@Id);"; // {id}
            try
            {
                object obj = Activator.CreateInstance(type);
                using SQLiteConnection connection = new SQLiteConnection(connectionString);
                connection.Open();
                using SQLiteCommand SQLiteCommand = new SQLiteCommand(strSql, connection);
                SQLiteCommand.Parameters.AddWithValue("@Id", id);
                SQLiteDataReader reader = SQLiteCommand.ExecuteReader();
                if (reader.Read())
                {
                    DataReaderMethod(type, obj, reader);
                    return (T)obj;

                }
                else
                {
                    return default;
                }
            }
            catch (Exception ex)
            {
                LogHelper.WriteLog(string.Format("日志，SingleQuery执行出错, 泛型：{0}，脚本：{1}，错误信息：{0}.", typeof(T).GetType().Name, strSql, ex.Message));
                throw;
            }
        }

        /// <summary>
        /// 泛型方法查询数据实体
        /// </summary>
        /// <typeparam name="T">泛型</typeparam>
        /// <param name="id">id</param>
        /// <returns></returns>
        public T SingleQuery<T>(string id)
        {
            var strSql = SqlCache<T>.QueryString + $" AND (Id='{id}');";
            //Type type = typeof(T);
            //var strSql = $"SELECT {GetFiledsByType(type)} FROM [{type.Name}] where (Id='{id}');";
            try
            {
                return ExecuteSingleQuery<T>(strSql);
            }
            catch (Exception ex)
            {
                LogHelper.WriteLog(string.Format("日志，SingleQuery执行出错, 泛型：{0}，脚本：{1}，错误信息：{2}。", typeof(T).GetType().Name, strSql, ex.Message));
                throw;
            }
        }

        /// <summary>
        /// 泛型方法查询数据实体
        /// </summary>
        /// <typeparam name="T">泛型</typeparam>
        /// <param name="id">id</param>
        /// <returns></returns>
        public T SingleQuery<T>(Guid id)
        {
            //Type type = typeof(T);
            var strSql = SqlCache<T>.QueryString + $" AND (Id='{id}');";
            //var strSql = $"SELECT {GetFiledsByType(type)} FROM [{type.Name}] where (Id='{id.ToString()}');";
            try
            {
                return ExecuteSingleQuery<T>(strSql);
            }
            catch (Exception ex)
            {
                LogHelper.WriteLog(string.Format("日志，SingleQuery执行出错, 泛型：{0}，脚本：{1}，错误信息：{2}。", typeof(T).GetType().Name, strSql, ex.Message));
                throw;
            }
        }

        /// <summary>
        /// 泛型方法根据查询条件查询数据实体
        /// </summary>
        /// <typeparam name="T">泛型</typeparam>
        /// <param name="sqlwhere">查询条件</param>
        /// <returns></returns>
        public T SingleWhereQuery<T>(string sqlwhere)
        {
            if (!sqlwhere.ToLower().Contains("and")) sqlwhere = $" AND {sqlwhere}";
            var strSql = SqlCache<T>.QueryString + $" {sqlwhere};";
            try
            {
                return ExecuteSingleQuery<T>(strSql);
            }
            catch (Exception ex)
            {
                LogHelper.WriteLog(string.Format("日志，SingleWhereQuery执行出错, 泛型：{0}，脚本：{1}，错误信息：{2}。", typeof(T).GetType().Name, strSql, ex.Message));
                throw;
            }
        }

        /// <summary>
        /// 执行泛型方法查询
        /// </summary>
        /// <typeparam name="T">泛型</typeparam>
        /// <param name="strSql">数据库脚本</param>
        /// <returns></returns>
        private static T ExecuteSingleQuery<T>(string strSql)
        {
            Type type = typeof(T);
            object obj = Activator.CreateInstance(type);
            SQLiteConnection connection = new SQLiteConnection(connectionString);
            connection.Open();
            SQLiteCommand SQLiteCommand = new SQLiteCommand(strSql, connection);
            SQLiteDataReader reader = SQLiteCommand.ExecuteReader();
            if (reader.Read())
            {
                DataReaderMethod(type, obj, reader);
                return (T)obj;
            }
            else
            {
                return default;
            }
        }

        /// <summary>
        /// DataReader方法
        /// </summary>
        /// <param name="type">泛型类型</param>
        /// <param name="obj">数据对象</param>
        /// <param name="reader">DataReader对象</param>
        private static void DataReaderMethod(Type type, object obj, SQLiteDataReader reader)
        {
            foreach (var prop in type.GetProperties())
            {
                try
                {
                    if (!prop.IsValidSqlField()) continue;

                    //if (!prop.PropertyType.IsPublic || !prop.CanWrite) continue;

                    //if (prop.Name == "EnableLogging")
                    //{
                    //    var dsfs = prop.PropertyType.Name; //Boolean
                    //    var dd232 = reader[prop.Name];
                    //    var dsfsfs = dd232;
                    //}

                    object value = (reader[prop.Name] == DBNull.Value) ? null : reader[prop.Name];

                    if (prop.PropertyType.Name == "Guid")
                    {
                        value = (reader[prop.Name] == DBNull.Value) ? default : new Guid(reader[prop.Name].ToString());
                    }

                    if (prop.PropertyType.Name == "Nullable`1")
                    {
                        string fullName = prop.PropertyType.FullName;
                        var isInt = fullName.Contains("System.Int32") || fullName.Contains("System.Int64");
                        var isDouble = fullName.Contains("System.Double");
                        if (isInt) value = value == null ? 0 : Convert.ToInt32(value);
                        if (isDouble) value = value == null ? 0 : Convert.ToDouble(value);
                    }

                    if (prop.GetMethod.ReturnParameter.ParameterType.Name == "Int32")
                    {
                        value = Convert.ToInt32(value);
                    }

                    if (prop.GetMethod.ReturnParameter.ParameterType.Name == "Int64")
                    {
                        value = Convert.ToInt32(value);
                    }

                    if (prop.PropertyType.Name == "Boolean")
                    {
                        value = (reader[prop.Name] == DBNull.Value) ? false : value;
                    }

                    if (prop.PropertyType.Name == "String")
                    {
                        prop.SetValue(obj, value);
                    }
                    else
                    {
                        try
                        {
                            prop.SetValue(obj, SqlNull(value));
                        }
                        catch (Exception)
                        {
                            throw;
                        }
                    }

                    #region 记录数据读取日志

                    //var logString = string.Format("日志，属性：{0}，类型：{1}，值：{2}", prop.Name, prop.PropertyType.Name, value);
                    //LogHelper.WriteLog(logString);
                    //System.Console.WriteLine(logString);
                    //LogUtils.LogInfo("ListQuery", Developer.MJ, logString);

                    #endregion
                }
                catch (Exception ex)
                {
                    LogHelper.WriteLog(string.Format("日志，DataReaderMethod执行出错，错误信息：{0}.", ex.Message));
                    throw;
                }
            }
        }

        #endregion

        #region 泛型方法查询列表数据

        /// <summary>
        /// 泛型方法查询数据列表
        /// </summary>
        /// <typeparam name="T">泛型</typeparam>
        /// <returns></returns>
        public List<T> ListQuery<T>()
        {
            Type type = typeof(T); // User是关键字，因此要包成[User]
            var strSql = $"SELECT {GetFiledsByType(type)} FROM [{type.Name}] ;";
            return this.ListQuery<T>(strSql);
        }

        /// <summary>
        /// 泛型方法查询数据列表
        /// </summary>
        /// <typeparam name="T">泛型</typeparam>
        /// <param name="strSql">数据库脚本</param>
        /// <returns></returns>
        public List<T> ListQuery<T>(string strSql)
        {
            try
            {
                List<T> list = null; // new List<T>();
                Type type = typeof(T); // User是关键字，因此要包成[User]
                using (SQLiteConnection connection = new SQLiteConnection(connectionString))
                {
                    connection.Open();
                    using SQLiteCommand cmd = new SQLiteCommand(strSql, connection);
                    using SQLiteDataReader reader = cmd.ExecuteReader();
                    if (reader.HasRows)
                    {
                        list = new List<T>();
                    }
                    while (reader.Read()) // 开始读取下一条数据
                    {
                        object obj = Activator.CreateInstance(type);

                        foreach (var prop in type.GetProperties())
                        {
                            //var b = Type.GetType(prop.PropertyType.FullName); //属性类型

                            //if (prop.Name == "Port")
                            //{
                            //    var e = prop.Name;
                            //}

                            //if (prop.Name == "ModifierId")
                            //{
                            //    var f = prop.Name;
                            //}

                            try
                            {
                                if (!prop.PropertyType.IsPublic || !prop.CanWrite) continue;

                                object value = (reader[prop.Name] == DBNull.Value) ? null : reader[prop.Name];

                                if (prop.PropertyType.Name == "Guid")
                                {
                                    value = (reader[prop.Name] == DBNull.Value) ? default : new Guid(reader[prop.Name].ToString());
                                }

                                var type1 = prop.GetMethod.ReturnParameter.ParameterType.Name;
                                var type2 = prop.PropertyType.Name;

                                if (prop.PropertyType.Name == "Nullable`1")
                                {
                                    string fullName = prop.PropertyType.FullName;
                                    var isInt = fullName.Contains("System.Int32") || fullName.Contains("System.Int64");
                                    if (isInt) value = value == null ? 0 : Convert.ToInt32(value);
                                }

                                // The the type of the property
                                Type columnType = prop.PropertyType;

                                // We need to check whether the property is NULLABLE
                                if (prop.PropertyType.IsGenericType && prop.PropertyType.GetGenericTypeDefinition() == typeof(Nullable<>))
                                {
                                    // If it is NULLABLE, then get the underlying type. eg if "Nullable<int>" then this will return just "int"
                                    columnType = prop.PropertyType.GetGenericArguments()[0];
                                }

                                //if (type1.ToLower().Contains("int") || type2.ToLower().Contains("int"))
                                //{
                                //    var tt = type1;
                                //}

                                if (prop.GetMethod.ReturnParameter.ParameterType.Name == "Int32")
                                {
                                    value = Convert.ToInt32(value);
                                }

                                if (prop.GetMethod.ReturnParameter.ParameterType.Name == "Int64")
                                {
                                    value = Convert.ToInt32(value);
                                }

                                if (prop.PropertyType.Name == "Boolean")
                                {
                                    value = (reader[prop.Name] == DBNull.Value) ? false : value;
                                }

                                if (prop.PropertyType.Name == "String")
                                {
                                    prop.SetValue(obj, value);
                                }
                                else
                                {
                                    prop.SetValue(obj, SqlNull(value));
                                }

                                ////第五步：使用
                                ////LogHelper.WriteLog(string.Format("***************************************"));
                                //var logString = string.Format("日志，泛型：{0}，属性：{1}，类型：{2}，类型1：{3}，值：{4}", typeof(T).GetType().Name, prop.Name, prop.PropertyType.Name, prop.GetMethod.ReturnParameter.ParameterType.Name, value);
                                //LogHelper.WriteLog(logString);
                                ////LogHelper.WriteLog(string.Format("日志，值：{0}", value));
                                //System.Console.WriteLine(logString);
                                //LogUtils.LogInfo("ListQuery", Developer.MJ, logString);
                            }
                            catch (Exception ex)
                            {
                                var innerEx = ex.Message;
                                //第五步：使用
                                LogHelper.WriteLog(string.Format("日志，泛型：{0}，属性：{1}，值：{2}，错误信息：{3}", typeof(T).GetType().Name, prop.Name, (reader[prop.Name] == DBNull.Value) ? null : reader[prop.Name], ex.Message));

                                throw;
                            }

                        }

                        list.Add((T)obj);
                    }
                }
                return list;
            }
            catch (Exception ex)
            {
                //第五步：使用
                LogHelper.WriteLog(string.Format("日志，List<T> ListQuery<T>执行出错, 泛型：{0}，脚本：{1}，错误信息：{0}.", typeof(T).GetType().Name, strSql, ex.Message));
                throw;
            }
        }

        #endregion

        #region SqlNull

        public static object SqlNull(object obj)
        {
            if (obj == null || obj.ToString() == "")
            {
                return DBNull.Value;
            }
            else
            {
                return obj;
            }
        }

        #endregion

        #region 数据查询错误日志

        /// <summary>
        /// 数据查询错误日志
        /// </summary>
        /// <param name="message"></param>
        private void WriteErrorLog(string message)
        {
            System.Console.WriteLine(message);
            LogHelper.WriteLog(message);
            LogUtils.LogInfo("Delete", Developer.MJ, message);
        }

        #endregion

        #region 泛型数据库增删改

        /// <summary>
        /// 泛型数据库实体插入
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="t"></param>
        /// <returns></returns>
        public int Add<T>(T t)
        {
            try
            {
                Type type = typeof(T); // User是关键字，因此要包成[User]

                // 添加的时候不更新包含“LastModif”的字段
                //var addProperties = type.GetProperties().Where(a => a.Name.IndexOf("LastModif") == -1);
                //var properties = addProperties.Where(a => a.Name != "Id"); // 也不包含主键字段

                //var fields = string.Join(',', properties.Select(p => '[' + p.Name + ']'));
                //var parameters = string.Join(',', properties.Select(p => '@' + p.Name));
                //var strSql = $"INSERT INTO [{type.Name}] ( {fields} ) VALUES ( {parameters} );";
                //IEnumerable<PropertyInfo> properties = type.GetProperties();
                //var strSql = AddGenericCache<T>.GetSqlString(out properties);

                var strSql = SqlCache<T>.InsertString;
                IEnumerable<PropertyInfo> properties = type.GetProperties();

                using SQLiteConnection connection = new SQLiteConnection(connectionString);
                connection.Open();
                using SQLiteCommand SQLiteCommand = new SQLiteCommand(strSql, connection);
                foreach (var prop in properties)
                {
                    #region 字段允许Null值

                    //var isNullable = true; // 允许Null值
                    //if (prop.IsDefined(typeof(ColumnAttribute), true))
                    //{
                    //    foreach (ColumnAttribute column in prop.GetCustomAttributes(typeof(ColumnAttribute), true))
                    //    {
                    //        isNullable = column.Nullable;
                    //        System.Console.WriteLine($"{prop.Name}{column.Nullable}");
                    //    }
                    //}

                    #endregion

                    #region 注释

                    //if(prop.Name == "CreateTime")
                    //{
                    //    var p = prop.GetValue(t).ToString();
                    //    System.Console.WriteLine(p);

                    //    var aa = DateTime.Parse("1753/1/1 12:00:00");
                    //    System.Console.WriteLine(aa);
                    //}

                    //if (prop.Name == "F_Binary")
                    //{
                    //    var dataType = prop.PropertyType.FullName;

                    //    var type1 = "";
                    //}

                    //System.Console.WriteLine(prop.Name);

                    #endregion

                    object value = prop.GetValue(t);
                    Type piType = Type.GetType(prop.PropertyType.FullName); //属性类型
                    if (piType.Name == "Guid")
                    {
                        if (value == null) value = Guid.Empty;
                    }
                    if (piType.Name == "DateTime")
                    {
                        if (value.ToString() == "0001/1/1 0:00:00")
                        {
                            value = null;
                            //if (isNullable)
                            //{
                            //    value = null;
                            //}
                            //else
                            //{
                            //    value = DateTime.Parse("1753/1/1 12:00:00");
                            //}
                        }
                    }
                    SQLiteCommand.Parameters.AddWithValue($"@{prop.Name}", SqlNull(value));

                }

                // 不能将值 NULL 插入列 'CreateTime'，表 'SampleDB.dbo.DemoTable'；列不允许有 Null 值。INSERT 失败。
                // 要注意的就是，这里的IsNullable，不是说你可以插入null值，而是指DBNull.Value值。
                //sqlCmd.Parameters[0].IsNullable = true;
                //sqlCmd.Parameters[1].IsNullable = true;

                return SQLiteCommand.ExecuteNonQuery();

            }
            catch (Exception ex)
            {
                WriteErrorLog(ex.Message);

                throw;
            }
        }

        /// <summary>
        /// 泛型数据库实体删除
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="id">主键</param>
        /// <returns></returns>
        public int Delete<T>(int id)
        {
            try
            {
                Type type = typeof(T); // User是关键字，因此要包成[User]
                var strSql = $"DELETE FROM [{type.Name}] WHERE ([Id]=@Id);";

                using SQLiteConnection connection = new SQLiteConnection(connectionString);
                connection.Open();
                using SQLiteCommand SQLiteCommand = new SQLiteCommand(strSql, connection);
                SQLiteCommand.Parameters.AddWithValue("@Id", id);
                return SQLiteCommand.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                WriteErrorLog(ex.Message);
                throw;
            }
        }

        /// <summary>
        /// 根据查询条件删除数据
        /// </summary>
        /// <typeparam name="T">泛型</typeparam>
        /// <param name="sqlwhere">查询条件</param>
        /// <returns></returns>
        public int DeleteByWhereSql<T>(string sqlwhere)
        {
            try
            {
                // 这地方要考虑实体特性
                Type type = typeof(T);
                var strSql = $"DELETE FROM [{type.Name}] WHERE (1=1) {sqlwhere};";
                using SQLiteConnection connection = new SQLiteConnection(connectionString);
                connection.Open();
                using SQLiteCommand SQLiteCommand = new SQLiteCommand(strSql, connection);
                return SQLiteCommand.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                WriteErrorLog(ex.Message);
                throw;
            }
        }

        /// <summary>
        /// 泛型数据库实体更新
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="t"></param>
        /// <returns></returns>
        public int Update<T>(T t)
        {
            try
            {
                Type type = typeof(T); // User是关键字，因此要包成[User]

                //// 修改的时候不更新包含“Creat”的字段
                //var properties = type.GetProperties().Where(a => a.Name.IndexOf("Creat") == -1);
                //var editproperties = properties.Where(a => a.Name != "Id"); // 可编辑字段
                //var fieldValues = string.Join(',', editproperties.Select(p => "[" + p.Name + "]=@" + p.Name));
                //var strSql = $"UPDATE [{type.Name}] SET {fieldValues} WHERE (Id = @Id);";

                IEnumerable<System.Reflection.PropertyInfo> properties = type.GetProperties();
                var strSql = SqlCache<T>.UpdateString;

                using SQLiteConnection connection = new SQLiteConnection(connectionString);
                connection.Open();
                using SQLiteCommand SQLiteCommand = new SQLiteCommand(strSql, connection);
                foreach (var prop in properties)
                {
                    var value = prop.GetValue(t);
                    SQLiteCommand.Parameters.AddWithValue($"@{prop.Name}", SqlNull(value));
                }
                return SQLiteCommand.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                WriteErrorLog(ex.Message);
                throw;
            }
        }

        #endregion


    }
}
